package com.hzdl.car.util;

import com.hzdl.car.bean.Customer;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class CustomExcelUtil {


//    public static void main(String[] args) {
////        create2();
//
//    }
    public static ByteArrayOutputStream create2(List<Customer> list){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        // 获取List<Customer> list
//        List<Customer> list = new ArrayList<>();
//        for(int i = 0; i<20 ;i++){
//            list.add(new Customer("1111"+i , "lisi_"+i, 0, "四川","12344566766","呵呵呵",new Date()));
//        }

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("客户信息");

        // 设置单元格默认宽高
        sheet.setDefaultRowHeight((short)(30*20));
        sheet.setDefaultColumnWidth(20);


        //  合并第一行前7列
        CellRangeAddress region = new CellRangeAddress(0,0,0,6);
        sheet.addMergedRegion(region);
        CellRangeAddress region2 = new CellRangeAddress(1,1,0,6);
        sheet.addMergedRegion(region2);
        // 设置值
        int rownum = 0;
        HSSFRow row1 = sheet.createRow(rownum);
        HSSFCell row1_cell1 = row1.createCell(0);
        row1_cell1.setCellValue("客户信息");

        HSSFCellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);//左右居中
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中

        HSSFFont font1 = workbook.createFont();
        font1.setColor(HSSFColor.BLUE.index);// 设置字体颜色
        font1.setBold(true);
        font1.setFontName("华文彩云");
        font1.setFontHeight((short)400);

        cellStyle1.setFont(font1);// 设置字体样式
        row1_cell1.setCellStyle(cellStyle1);// 设置单元格样式

        // 第二行
        rownum++;
        HSSFRow row2 = sheet.createRow(rownum);
        HSSFCell row2_cell1 = row2.createCell(0);
        row2_cell1.setCellValue(" 总条数："+list.size()+"   时间："+sdf.format(new Date()));
        // 设置样式
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);//左右居中
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中

        HSSFFont font2 = workbook.createFont();
        font2.setColor(HSSFColor.GREEN.index);// 设置字体颜色
        font2.setFontName("宋体");
        font2.setFontHeight((short)200);

        cellStyle2.setFont(font2);// 设置字体样式
        row2_cell1.setCellStyle(cellStyle2);

        // 第3行
        rownum++;
        HSSFRow row3 = sheet.createRow(rownum);
        String[] arr = {"身份证号","客户姓名","客户性别","地址","联系电话","职位","创建时间"};
        for(int i =0 ; i<arr.length;i++){
            HSSFCell row3_cellx = row3.createCell(i);
            row3_cellx.setCellValue(arr[i]);
        }


        // 第4行 ~ 最后
        for(int i =0 ; i<list.size();i++){
            rownum++;
            Customer customer = list.get(i);

            HSSFRow rowx = sheet.createRow(rownum);

            HSSFCell rowx_cell1 = rowx.createCell(0);
            rowx_cell1.setCellValue(customer.getIdentity());

            HSSFCell rowx_cell2 = rowx.createCell(1);
            rowx_cell2.setCellValue(customer.getCustname());

            HSSFCell rowx_cell3 = rowx.createCell(2);
            rowx_cell3.setCellValue(customer.getSex()==1?"男":"女");

            HSSFCell rowx_cell4 = rowx.createCell(3);
            rowx_cell4.setCellValue(customer.getAddress());

            HSSFCell rowx_cell5 = rowx.createCell(4);
            rowx_cell5.setCellValue(customer.getPhone());

            HSSFCell rowx_cell6 = rowx.createCell(5);
            rowx_cell6.setCellValue(customer.getCareer());

            HSSFCell rowx_cell7 = rowx.createCell(6);
            rowx_cell7.setCellValue(sdf.format(customer.getCreatetime()));

        }


        try {
            // 保存到磁盘
            // workbook.write(new File("D://customs1001.xls"));
            // 输出流对象
            ByteArrayOutputStream stream = new ByteArrayOutputStream();
            workbook.write(stream);

            return stream;
        } catch (IOException e) {
            e.printStackTrace();
        }

        return null;
    }
    public static void create1(){
        // 生成 customs.xls文件到磁盘
        // 创建excel对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建sheet
        HSSFSheet sheet = workbook.createSheet("客户信息");

        // 设置值  -- 需要单元格
        // 创建行
        int rownum = 0;
        HSSFRow row1 = sheet.createRow(rownum);
        // 创建列
        HSSFCell row1_cell1 = row1.createCell(0);
        row1_cell1.setCellValue("hello");

        // 保存到磁盘
        try {
            workbook.write(new File("D://customs1001.xls"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

